﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// Summary description for DataHelper
/// </summary>
/// 
namespace LinkCard.AdminCP.Common
{
    public class DataHelper
    {
        public static string strConn33 = "";
        public static string strConnCorporate = "";
        public static string strConn = "";

        public const int len_ntext = 1073741823;

        #region connection string

        public static void GetConnectionCorporate()
        {
            if (strConnCorporate == "")
            {
                strConnCorporate = WebConfigurationManager.AppSettings["ConnectionStringCorporate"];
            }
        }

        public static void GetConnection()
        {
            if (strConn == "")
            {
                strConn = WebConfigurationManager.AppSettings["ConnectionString"];
            }
        }

        public static void GetConnection33()
        {
            if (strConn33 == "")
            {
                strConn33 = WebConfigurationManager.AppSettings["ConnectionString33"];
            }
        }

        #endregion

        #region execute querry command

        public static int ExecuteNonQuery33(string Sql, bool b)
        {
            int val = 0;
            try
            {
                GetConnection33();
                SqlConnection conn = new SqlConnection(strConn33);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);
                if (b)
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }

                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQuery33(string storedProcedure, SqlParameter param)
        {
            int val = 0;
            try
            {
                GetConnection33();
                SqlConnection conn = new SqlConnection(strConn33);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(param);
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQuery33(string storedProcedure, SqlParameter[] param)
        {
            int val = 0;
            try
            {
                GetConnection33();
                SqlConnection conn = new SqlConnection(strConn33);
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = storedProcedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQueryCorporate(string storedProcedure)
        {
            int val = 0;
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQueryCorporate(string Sql, bool b)
        {
            int val = 0;
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);
                if (b)
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }

                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQueryCorporate(string storedProcedure, SqlParameter param)
        {
            int val = 0;
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(param);
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQueryCorporate(string storedProcedure, SqlParameter[] param)
        {
            int val = 0;
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = storedProcedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }


        public static int ExecuteNonQuery(string storedProcedure)
        {
            int val = 0;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Sql">Câu lệnh Sql</param>
        /// <param name="b">true tương ứng Text.false tương ứng StoredProcedure</param>
        /// <returns></returns>

        public static int ExecuteNonQuery(string Sql, bool b)
        {
            int val = 0;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);
                if (b)
                {
                    cmd.CommandType = CommandType.Text;
                }
                else
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }

                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQuery(string storedProcedure, SqlParameter param)
        {
            int val = 0;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(param);
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static int ExecuteNonQuery(string storedProcedure, SqlParameter[] param)
        {
            int val = 0;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = storedProcedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                val = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        #endregion

        #region execute Scalar
        public static object ExecuteScalar(string storedProcedure)
        {
            object val = null;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                val = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }
        public static object ExecuteScalar(string storedProcedure, SqlParameter param)
        {
            object val = null;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(param);
                val = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }
        public static object ExecuteScalar(string storedProcedure, SqlParameter[] param)
        {
            object val = null;
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                val = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }
        #endregion

        #region Select table
        public static DataTable SelectTable33(string sqlString, bool store)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnection33();
                SqlConnection conn = new SqlConnection(strConn33);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTableCorporate(string sqlString, bool store)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTableCorporate(string storedProcedure)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTableCorporate(string storedProcedure, SqlParameter param)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                cmd.Parameters.Add(param);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTableCorporate(string storedProcedure, SqlParameter[] param)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnectionCorporate();
                SqlConnection conn = new SqlConnection(strConnCorporate);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTable(string sqlString, bool store)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTable(string storedProcedure)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTable(string storedProcedure, SqlParameter param)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                cmd.Parameters.Add(param);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataTable SelectTable(string storedProcedure, SqlParameter[] param)
        {
            DataTable val = new DataTable();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataSet SelectTableDataSet(string sqlString, bool store)
        {
            DataSet val = new DataSet();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        public static DataSet SelectTableDataSet(string storedProcedure, SqlParameter[] param)
        {
            DataSet val = new DataSet();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(val);
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return val;
        }

        #endregion

        #region Load page
        public static DataTable LoadPage(string storedProcedure, int startRecord, int page_size)
        {
            DataSet ds = new DataSet();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(ds, startRecord, page_size, "result");
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }
        public static DataTable LoadPage(string storedProcedure, int startRecord, int page_size, SqlParameter param)
        {
            DataSet ds = new DataSet();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                cmd.Parameters.Add(param);
                daSql.Fill(ds, startRecord, page_size, "result");
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }
        public static DataTable LoadPage(string storedProcedure, int startRecord, int page_size, SqlParameter[] param)
        {
            DataSet ds = new DataSet();
            try
            {
                GetConnection();
                SqlConnection conn = new SqlConnection(strConn);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < param.Length; i++)
                    cmd.Parameters.Add(param[i]);
                SqlDataAdapter daSql = new SqlDataAdapter(cmd);
                daSql.Fill(ds, startRecord, page_size, "result");
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds.Tables[0];
        }
        #endregion

        #region Phân trang trong SQL

        public static DataTable LoadPageInSQL(string storedProcedure, int pageIndex, int pageSize, ref int totalRecords)
        {
            GetConnection();
            

            using (var connection = new SqlConnection(strConn))
            {
                var table = new DataTable();
                var cmd = new SqlCommand(storedProcedure, connection)
                {
                    CommandType = CommandType.StoredProcedure,
                    CommandTimeout = 300
                };

                
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                cmd.Parameters.AddWithValue("@TotalRecords", totalRecords).Direction = ParameterDirection.Output;
                

                try
                {
                    var adapter = new SqlDataAdapter { SelectCommand = cmd };
                    adapter.Fill(table);
                    connection.Close();

                    if (table.Rows.Count > 0)
                    {
                        totalRecords = Convert.ToInt32(cmd.Parameters["@TotalRecords"].Value);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("Load page in SQL: " + ex.Message);
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
                return table;
            }
        }

        public static DataTable LoadPageInSQL(string storedProcedure, int pageIndex, int pageSize, ref int totalRecords, SqlParameter param)
        {
            GetConnection();


            using (var connection = new SqlConnection(strConn))
            {
                var table = new DataTable();
                var cmd = new SqlCommand(storedProcedure, connection)
                {
                    CommandType = CommandType.StoredProcedure,
                    CommandTimeout = 300
                };


                cmd.Parameters.AddWithValue("@Page", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                cmd.Parameters.AddWithValue("@TotalRecords", totalRecords).Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

                try
                {
                    var adapter = new SqlDataAdapter { SelectCommand = cmd };
                    adapter.Fill(table);
                    connection.Close();

                    if (table.Rows.Count > 0)
                    {
                        totalRecords = Convert.ToInt32(cmd.Parameters["@TotalRecords"].Value);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("Load page in SQL: " + ex.Message);
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
                return table;
            }
        }

        #endregion 


        public static object ExecuteScalar(string p, SqlParameter p1, SqlParameter p2)
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }

}